# demo01_pymysql.py
import pymysql
conn = pymysql.connect(  # 1. 创建数据库连接
    host="127.0.0.1", port=3306, user="root",
    passwd="tedu.cn", db="tedu1", charset="utf8"
)
cur = conn.cursor()  # 2. 创建游标, 游标: 对数据库进行操作
# 3. 对数据库的操作
# 3.1 ---------------建表-----------------
# create_bumen = """CREATE TABLE bumen(
#     dep_id INT, dep_name VARCHAR(30)
# );"""  # "Table 'bumen' already exists": 表已存在,不能重复创建
# cur.execute(create_bumen)  # 通过游标执行sql语句
# 3.2 ----------------给 bumen 插入数据---------------------
# insert_bumen = """INSERT INTO bumen VALUES(%s, %s)"""
# cur.execute(insert_bumen, (1, "OPS"))  # 单条插入
# cur.execute(insert_bumen, (2, "DEV"))
# cur.executemany(insert_bumen, [
#     (3, "TEST"), (4, "MARKET")
# ])
# 3.3 -----------------------查询数据-------------------
# select_bumen = "SELECT * FROM bumen;"
# cur.execute(select_bumen)
# # 获取查询的数据
# print(cur.fetchone())  # 获取单条
# print(cur.fetchmany(2))  # 获取多条数据
# print(cur.fetchall())  # 获取剩余所有的数据
# 3.4----------------更新数据-------------------
update_bumen = "UPDATE bumen SET dep_name=%s WHERE dep_id=%s"
cur.execute(update_bumen, ("QIFENZU", 2))
# 3.5 ---------------删除数据--------------------
delete_bumen = "DELETE FROM bumen WHERE dep_id=%s"
cur.execute(delete_bumen, (1, ))
# 4. 提交修改并且关闭资源
conn.commit()  # 提交对数据库的写操作
conn.close()
